package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSalg 
{
	/**
	 * Initializes instance variables
	 */
	private Connection con;
	private DBSalgSaede dbsalgsaede;
	
	/** Creates connection to the database and creates an instance of DBSalg*/
	public DBSalg()
	{
		con = DbConnection1.getInstance().getDBcon();
		dbsalgsaede = new DBSalgSaede();
	}
	
	/** getAllsalg finds all Salg with
	  * @param retrieveAssociation (tells weather or not the returned Salg
	  *  needs to be associated with a Forestilling and a Kunde)
	  * @return
	  */
	public ArrayList<Salg> getAllSalg(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	/** getSalg finds a specific salg with
	 * @param salgId
	 * @param retrieveAssociation (tells weather or not the returned Salg
	  *  needs to be associated with a Forestilling and a Kunde)
	 * @return
	 */
	public Salg getSalg(int salgId, boolean retrieveAssociation)
	{
		String wClause = " salgId = '" + salgId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	/** insertSalg creates a salg SQL query to be inserted into database with
	 * 
	 * @param salg
	 * @return
	 * @throws Exception (If Salg not created)
	 */
	public int insertSalg(Salg salg) throws Exception
	{	
		int rc = -1;
		int k = 0;
		if(salg.getKunde() != null)
		{
			k = salg.getKunde().getTelefonNr();
		}
		else
		{
			k = 0;
		}
		String query ="INSERT INTO Salg(salgId, datoOprettet, BilletAntal, SalgsType, forestilling, Kunde) VALUES('"+
		
				salg.getSalgId() + "','" +
				salg.getDatoOprettet() + "','" +
				salg.getBilletAntal() + "','" +
				salg.getSalgType() + "','" +
				salg.getForestilling().getForestillingId() + "','" +
				k + "')";
		//	System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			
			for(Saede saede : salg.getSaede())
			{
				//	System.out.println("Forelykke : " + salg.getSalgId());
				dbsalgsaede.insertSalgSaede(saede, salg);
			}
			
			//System.out.println("salgsaede");
			stmt.close();
			
		}//end try
		catch(SQLException ex)
		{
			//System.out.println("Salg ikke oprettet");
			throw new Exception ("Salg is not inserted correct");
		}
		return(rc);
	}
	
	/** deleteSalgremoves a Salg from database with
	 * 
	 * @param salgId
	 * @return
	 */
	public int delete(int salgId)
	{
		int rc=-1;
		
		String query ="DELETE FROM salg WHERE salgId = '" + 
				salgId + "'";
		//System.out.println(query);
		try
		{ //delete from salg
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Delete exception in Salg db: "+ex);
		}
		return(rc);
	}
	
	/** updateForestilling creates an update salg SQL query to update a salg in the database with
	 * 
	 * @param salg
	 * @return
	 */
	public int updateSalg(Salg salg)
	{
		Salg salgObj  = salg;
		int rc=-1;

		String query="UPDATE salg SET "+
				"BilletAntal ='"+ salgObj.getBilletAntal() + "', " +
				"SalgsType ='"+ salgObj.getSalgType() + "'" +
				"WHERE salgId = '"+ salgObj.getSalgId() + "'";
		//System.out.println("Update query:" + query);
		try
		{ // update Salg
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex)
		{
			//System.out.println("Update exception in salg db: "+ex);
		}
		return(rc);
	}
	
	/** singleWhere is used when we only want to select one Salg
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Salg
	 *  needs to be associated with a Forestilling and a Kunde)
	 * @return
	 */
	private Salg singleWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		Salg salg = new Salg();	

		String query =  buildQuery(wClause);
		//System.out.println(query);
		try
		{ // read the salg from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if( results.next() )
			{
				salg = buildSalg(results);
				
				stmt.close();
				if(retrieveAssociation)
				{   
					//association to Kunde
					DBKunde dbKunde = new DBKunde();
					Kunde kunde = dbKunde.getKunde(salg.getKunde().getTelefonNr(), false);
					//System.out.println("Kunde is selected ");
					salg.setKunde(kunde);
					
					//association to Forestilling
					DBForestilling dbForestilling = new DBForestilling();
					Forestilling forestilling = dbForestilling.getForestilling(salg.getForestilling().getForestillingId(), false);
					//System.out.println("Forestilling is selected ");
					salg.setForestilling(forestilling);
				}		
			}
			else{ //if no salg found
				salg = null;
			}
		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception: "+e);
		}
		return salg;
	}
	
	/** mischWere is used whenever we want to select more than one salg or all salg
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned Salg needs to be associated with a Forestilling and a Kunde)
	 * @return
	 */
	private ArrayList<Salg> miscWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		ArrayList<Salg> list = new ArrayList<Salg>();	
		String query =  buildQuery(wClause);

		try
		{ // read the salg from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next())
			{
				Salg salgObj = new Salg();
				salgObj = buildSalg(results);	
				list.add(salgObj);
			//System.out.println("\nSalg:" + salgObj.getSalgId() + "\nBilletter:" + salgObj.getBilletAntal() + "\nKunde: " + salgObj.getKunde().getTelefonNr() + "\n");
			}//end while
			stmt.close();     
			if(retrieveAssociation)
			{   
				for(Salg salgObj : list)
				{
					//association to Kunde
					DBKunde dbKunde = new DBKunde();
					Kunde kunde = dbKunde.getKunde(salgObj.getKunde().getTelefonNr(), false);
					//System.out.println("Kunde is selected ");
					salgObj.setKunde(kunde);
					
					//association to Forestilling
					DBForestilling dbForestilling = new DBForestilling();
					Forestilling forestilling = dbForestilling.getForestilling(salgObj.getForestilling().getForestillingId(), false);
					//System.out.println("Forestilling is selected ");
					salgObj.setForestilling(forestilling);
				}
			}//end if   

		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a Salg and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause) 
	{
		String query="select * from Salg";

		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}
		return query;
	}
	
	/** buildSalg creates a salg object with
	 * 
	 * @param results (used to bring attributes to salgObj)
	 * @return
	 */
	private Salg buildSalg(ResultSet results) 
	{
		Salg salgObj = new Salg();
		try
		{ // the columns from the table person  are used
			salgObj.setSalgId(results.getInt("salgId"));
			salgObj.setDatoOprettet(results.getString("datoOprettet"));
			salgObj.setBilletAntal(results.getByte("billetAntal"));
			salgObj.setSalgType(results.getBoolean("salgsType"));	
			salgObj.setForestilling(new Forestilling(results.getInt("forestilling")));
			salgObj.setKunde(new Kunde(results.getInt("kunde")));
		}
		catch(Exception e)
		{
			//System.out.println("error in building the Salg object");
		}
		return salgObj;
	}
}
